/**
 * @Title: Standard.java
 * @Package tbg 
 * @Description: TODO
 * @author tbc
 * @date 2016年6月11日 下午8:38:24
 * @version
 */
package tbg;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.Properties;
import java.util.Scanner;
import java.util.concurrent.TimeUnit;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import ch.qos.logback.classic.net.SyslogAppender;
import net.tt.util.CloseUtil;
import net.tt.util.PropUtil;

/**
 * h:\\Users\\tbc\\Desktop\\标准库.xlsx <br>
 * h:\\Users\\tbc\\Desktop\\奥铃轻卡 - 副本.xlsx
 * 
 * @author tbc
 * @version 1.0 create:{2016年6月11日 下午8:38:24}
 * 
 */
public class Standard {
	private Logger log = LoggerFactory.getLogger(Standard.class);

	private static XSSFWorkbook wb;
	private static XSSFSheet sheet;
	// 标准库
	private static XSSFWorkbook _wb;
	private static XSSFSheet _sheet;

	private static int lastColumn;

	private static double toDouble(String source) {
		return toDouble(source, 0.0);
	}

	private static double toDouble(String source, Double def) {
		try {
			return Double.parseDouble(source);
		} catch (Exception e) {
			return def;
		}
	}

	public static void main(String[] args) throws InterruptedException {

		System.out.println("拖动标准库到命令窗口：");
		@SuppressWarnings("resource")
		Scanner s = new Scanner(System.in);
		// 要处理的源文件路径
		String from = null;
		// 标准库文件路径
		String sPath = null;
		//
		sPath = s.nextLine();
		System.out.println("标准库文件： " + sPath);
		System.out.println("拖动要处理的文件到命令窗口：");
		from = s.nextLine();
		System.out.println("要处理的文件" + from);

		System.out.println("初始化 ...");
		init(from, sPath);
		System.out.println("初始化完成");
		System.out.println("开始处理...");
		process();
		System.out.println("处理完成");
		System.out.println("写回Excel文件...");
		write(from);
		System.out.println("程序即将退出...");
		TimeUnit.SECONDS.sleep(2);
	}

	public static void init(String from, String sPath) throws InterruptedException {
		if (from.startsWith("\"")) {
			System.out.println("文件路径带引号，尝试去除...");
			from = from.substring(1, from.length() - 1);
		}
		System.out.println("--> 正在加载文件：" + from);
		// 输入流应该啥时候关闭。。。？？？
		InputStream in = null;
		try {
			in = new FileInputStream(from);
			System.out.println("--> 加载成功，创建工作簿对象...");
			wb = new XSSFWorkbook(in);
			System.out.println("--> 工作簿对象创建完成");
		} catch (FileNotFoundException e) {
			System.out.println("读取文件失败，文件不存在或者文件名有特殊符号。。。最好是只有英文字母");
			System.out.println("程序自动退出，修改文件路径或者文件名再试试");
			TimeUnit.SECONDS.sleep(2);
			e.printStackTrace();
		} catch (IOException e) {
			System.out.println("加载excel失败，不知道为什么 ，程序退出。。。");
			System.out.println(e.getMessage());
			TimeUnit.SECONDS.sleep(2);
		}

		try {
			System.out.println("--> 读取sheet页...");
			sheet = wb.getSheet("1");
			if (sheet == null) {
				System.out.println("读取sheet页失败");
				TimeUnit.SECONDS.sleep(2);
				System.exit(0);
			}
		} catch (Exception e) {
			System.out.println();
		}
		System.out.println("--> 读取sheet页成功");

		// 标准库
		File standardFile = new File(sPath);
		InputStream standardIn = null;
		try {
			System.out.println("--> 加载标准库文件...");
			standardIn = new FileInputStream(standardFile);
			System.out.println("--> 加载成功 ，创建标准库工作簿对象...");
			_wb = new XSSFWorkbook(standardIn);
			System.out.println("--> 创建标准库工作簿对象成功");
		} catch (FileNotFoundException e) {
			System.out.println("--> 加载标准库文件失败");
			TimeUnit.SECONDS.sleep(2);
			e.printStackTrace();
		} catch (IOException e) {
			System.out.println("--> 创建标准库工作簿对象失败");
			TimeUnit.SECONDS.sleep(2);
			e.printStackTrace();
		}

		_sheet = _wb.getSheetAt(0);

		// 标准库的头行
		XSSFRow _row0 = _sheet.getRow(0);
		// 源文件的头行
		XSSFRow row0 = sheet.getRow(0);
		// 要对比的列序号
		lastColumn = row0.getLastCellNum() - 1;

		// 源文件头行增加标题列
		for (int i = 1; i < _row0.getLastCellNum(); i++) {
			XSSFCell newCell = row0.createCell(row0.getLastCellNum());
			newCell.setCellValue(_row0.getCell(i).getStringCellValue());
		}

	}

	public static void process() {

		for (int i = 1; i <= sheet.getLastRowNum(); i++) {
			XSSFRow row = sheet.getRow(i);
			XSSFCell cell = row.getCell(lastColumn);

			int type = cell.getCellType();
			String value = "";
			if (type == 0) {
				value = cell.getRawValue();
			} else {
				value = cell.getStringCellValue();
			}

			XSSFRow b = search(value);

			if (b != null) {
				for (int j = 1; j < b.getLastCellNum(); j++) {
					XSSFCell bCell = b.getCell(j);
					XSSFCell sCell = row.createCell(lastColumn + j);
					// 大小写问题？？？

					int b_type = bCell.getCellType();

					if (b_type == 0) {
						sCell.setCellValue(bCell.getNumericCellValue());
					} else {
						sCell.setCellValue(bCell.getStringCellValue());
					}

				}
			}
		}

	}

	/**
	 * 从标准库中比对
	 * 
	 * @author tbc
	 * @version 1.0 create:{2016年6月11日 下午9:48:56}
	 */
	public static XSSFRow search(String value) {
		if (value == null) {
			return null;
		}
		for (int i = 1; i <= _sheet.getLastRowNum(); i++) {
			XSSFRow row = _sheet.getRow(i);
			XSSFCell cell = row.getCell(0);
			int type = cell.getCellType();
			String _value = "";
			if (type == 0) {
				_value = cell.getRawValue();
			} else {
				_value = cell.getStringCellValue();
			}
			if (value.toLowerCase().equals(_value.toLowerCase())) {
				return row;
			}
		}
		return null;
	}

	public static void write(String from) throws InterruptedException {
		OutputStream out = null;
		try {
			out = new FileOutputStream(from);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}

		try {
			wb.write(out);
			System.out.println("写入完成，清理资源...");
		} catch (IOException e) {
			System.out.println("写回Excel文件失败");
			TimeUnit.SECONDS.sleep(2);
			e.printStackTrace();
		} finally {
			if (out != null) {
				CloseUtil.close(out);
			}
		}

	}
}
